import pymysql
import sqlalchemy
import pandas as pd
from urllib import parse
import pandas as pd

class Sql():
    def __init__(self):
        self.engine,self.cursor = self.creat_engine()

    def read_sql_param(self,path='./sql_param.txt'):
        print('')
        print('开始读取数据库参数')
        df = pd.read_csv(path)
        host = df.host.values[0]
        user = df.user.values[0]
        passwd = df.passwd.values[0]
        port = df.port.values[0]
        schema_database = df.schema_database.values[0]
        print('读取完毕')
        print('')
        return str(host), str(user), str(passwd), int(port), str(schema_database)

    def creat_engine(self):
        host, user, passwd, port, schema_database = self.read_sql_param()
        # pwd = parse.quote_plus(passwd)
        try:
            engine = sqlalchemy.create_engine(
                f'mysql+pymysql://{user}:{passwd}@{host}:{port}/{schema_database}?charset=utf8')
            print('连库成功1')
            db = pymysql.connect(host=host, port=port, user=user, passwd=passwd, database=schema_database)
            cursor = db.cursor()
            print('连库成功2')
            return engine, cursor
        except Exception as exp:
            print('连接数据库失败：', exp)
            return 0
    #
    def ReadSql(self,id_lst):

        sql_dfo = fr'''
             
with main as (
		select distinct pjuuid,estdate,industryphy,industryco,reccaprmb,regcap,pripid
		from cs_mid_baseinfo_valid
		where estdate < date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)  
		
) ,

pjjd_list2  as 
(
select 	
distinct 
	pjuuid	,
	AZ1	,
	AZ2	,
	AZ3	,
	AZ4	,
	AZ5	,
	AZ6	,
	AZ7	,
	AZ8	,
	AZ9	,
	AZ10	,
	AZ11	,
	AZ12	,
	AZ13	,
	A0	,
	A1	,
	A2	,
	A3	,
	A4	,
	A5	,
	A6	,
	A7	,
	A8	,
	A9	,
	A10	,
	A11	,
	A12	,
	A13	,
	A14	,
	A16	,
	A18	,
	A20	,
	A21	,
	A22	,
	A23	,
	A24	,
	A25	,
	A26	,
	A27	,
	A28	,
	A29	,
	A30	,
	A31	,
	A32	,
	A33	,
	AZ14	
from 	pjjd_list	
where pjuuid is not null and pjuuid <> ''
and pjuuid in ({id_lst})
),

target as (


SELECT
  t1.pjuuid,
	AZ2 as credit_id,
case 			
when cast(substr(AZ14,1,2) as signed)  in 	(1, 2, 3, 4, 5)	then 	"A"
when cast(substr(AZ14,1,2) as signed)  in 	(6, 9, 12, 25, 30, 11)	then 	"B06091230C25"
when cast(substr(AZ14,1,2) as signed)  in 	(13, 14, 15, 27, 41, 42, 16)	then 	"C13_15274142"
when cast(substr(AZ14,1,2) as signed)  in 	(17, 18)	then 	"C1718"
when cast(substr(AZ14,1,2) as signed)  in 	(19)	then 	"C19"
when cast(substr(AZ14,1,2) as signed)  in 	(20, 21, 22, 23, 24)	then 	"C20_24"
when cast(substr(AZ14,1,2) as signed)  in 	(26, 28)	then 	"C2628"
when cast(substr(AZ14,1,2) as signed)  in 	(29)	then 	"C29"
when cast(substr(AZ14,1,2) as signed)  in 	(31, 32, 33, 38, 39, 40, 37)	then 	"C31_3338_40"
when cast(substr(AZ14,1,2) as signed)  in 	(34, 35)	then 	"C3435"
when cast(substr(AZ14,1,2) as signed)  in 	(36)	then 	"C36"
when cast(substr(AZ14,1,2) as signed)  in 	(43, 61, 62, 64, 65, 69, 70, 71, 72, 74, 75, 81, 82, 90)	then 	"C43_R90"
when cast(substr(AZ14,1,2) as signed)  in 	(44, 45, 46)	then 	"D44_46"
when cast(substr(AZ14,1,2) as signed)  in 	(49, 50, 47, 48)	then 	"E4950"
when cast(substr(AZ14,1,2) as signed)  in 	(51, 52)	then 	"F"
when cast(substr(AZ14,1,2) as signed)  in 	(53, 54, 55, 59, 60)	then 	"G53_5559_60"
when cast(substr(AZ14,1,2) as signed)  in 	(58, 63, 73, 76, 77, 78, 79, 80, 83, 84, 85, 86, 87, 88, 89, 92)	then 	"G58_S92"
else 'bup' end as model_type ,
	CASE
	WHEN A25 LIKE '%%是%%' THEN
	1 ELSE 0 
	END AS A25,-- 环境管理制度 A25
CASE
	
	WHEN A6 LIKE '%%是%%' THEN
	1 ELSE 0 
	END AS A6, -- 是否生产绿色产品 A6
case when  A7  like '是' or A8 like '是' then 1 else 0 end as cust , -- 下游应用方向 cust 
case when A9 like '是' or A10  like '是' or A11 like '是' then 1 else 0 end  as supp ,  -- 节能环保原材料使用
case when  A12 like '是' or A13  like '是' or A14 like '是' then 1 else 0 end  as equip, -- 低能耗生产设备使用
case when  A16 != 'x3' then 1 else 0 end as feishui , -- 废水排放管理
case when  A18 != 'x3' then 1 else 0 end as feiqi ,-- 废气排放管理
case when  A20 != 'x3' then 1 else 0 end as feizha , -- 废渣排放管理
CASE
    WHEN A21 = '否' THEN 1
    WHEN A21 != '否' AND A24 = '是' THEN 0.5
    WHEN A21 != '否' AND (A22 = '是' OR A23 = '是') THEN 1
    ELSE 0  end as weifei ,-- 危险废弃物排放管理 
case when A26 = '是' then 1 else 0 end  as A26, -- A26是否采用电子化运营管理
case when  A27 = '是' or  A28 = '是' then 1 else 0 end  as jianzhu, -- jianzhu 绿色建筑及建筑节能
case when A29 = '是' or A30 = '是' then 1 else 0 end as shebei, -- shebei设备节能
(
        CASE
            WHEN A0 IS NOT NULL AND A0 <> '' AND A0 <> '非绿' THEN A2
            ELSE 0
        END +
        CASE
            WHEN A3 IS NOT NULL AND A3 <> '' AND A3 <> '非绿' THEN A5
            ELSE 0
        END
    ) / 100.0 AS lyzb,  -- 绿营占比
  A31 / (GREATEST(
        COALESCE(t1.A33, 0),
        COALESCE(t2.xssr, 0),
        COALESCE(t3.vendinc, 0)
    ) * (100 - coalesce(AZ11,0)) / 100 ) AS ynqd, -- 用能强度
		A32 / (GREATEST(
        COALESCE(t1.A33, 0),
        COALESCE(t2.xssr, 0),
        COALESCE(t3.vendinc, 0)
    ) * (100 - coalesce(AZ11,0)) / 100 ) as ysqd   -- 用水强度

from pjjd_list2 t1 

left join

(select pjuuid, substr(sbny,1,4) as sbny ,sum(xssr) as xssr  
from 
zx_swg_nsxx_xssr_valid
where substr(sbny,1,4) >  year(date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 10 year))-- year(date_sub(now(),interval 10 year))
group by pjuuid,substr(sbny,1,4) 
)

t2

on  t1.AZ13 = t2.sbny and t1.pjuuid = t2.pjuuid

left join 

(select pjuuid,substr(year,1,4) as year ,sum(VENDINC) as VENDINC
from cs_yr_asset_valid 
where substr(year,1,4) >  year(date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 10 year)) -- year(DATE_SUB(now(),interval 10 year))
group by pjuuid,substr(year,1,4) 
) t3 
on t1.AZ13 = t3.year and t1.pjuuid = t3.pjuuid
where t1.pjuuid is not null and t1.pjuuid != ''


),



-- 已经去重

qycxrd_cnt_12m AS (
-- 企业诚信认定
select t1.pjuuid,t2.qycxrd_cnt_12m  
from 
main t1  left join 
(
SELECT DISTINCT
	pjuuid,
	rdsj as dt ,
	1 as qycxrd_cnt_12m
FROM
	zx_gsj_xyglsfqy_valid 
WHERE rdsj = year(date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)) 

UNION ALL

SELECT DISTINCT
	pjuuid,
	nd dt ,
	1 as qycxrd_cnt_12m
FROM
	zx_gsj_szmdxx_valid
WHERE nd = year(date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)) 

) t2 on  t1.pjuuid = t2.pjuuid
),

-- 
-- 
-- clns 成立年数
clns AS (
select pjuuid ,
				floor(DATEDIFF(STR_TO_DATE(now(),'%%Y-%%m-%%d'),estdate)/ 365 ) AS clns
from 
	main 
),


-- 
-- 
-- 企业主营范围变更次数
zybgcs_12m_cnt as (
select t1.pjuuid ,t2.zybgcs_12m_cnt
from 
main t1 
left join 
(
SELECT DISTINCT pjuuid,1 as zybgcs_12m_cnt 
from cs_mid_altitem_valid
where altitem = 12
and  datediff(now(),altdate) < 365  and datediff(now(),altdate) > 0
) t2  

on t1.pjuuid = t2.pjuuid 
),

sum_yjse as 
(
SELECT
	t1.pjuuid,
	sum( t2.yjse ) AS sum_yjse ,
	count(t2.yjse) as ttm_nsmonths
FROM
	main t1
	LEFT JOIN (
SELECT
	pjuuid,
	yjse 
FROM
	zx_swg_nsxx_sk_valid 
WHERE
	datediff( now(), str_to_date( concat( rkny, '-01' ), '%%Y-%%m-%%d' ) ) < 365 AND datediff( now(), str_to_date( concat( rkny, '-01' ), '%%Y-%%m-%%d' ) ) > 0 
	) t2 ON t1.pjuuid = t2.pjuuid 
GROUP BY
	t1.pjuuid
),
-- 



-- pwxk  排污许可
pwxk as (
select t1.pjuuid ,t2.pwxk
from main t1 left join 
(
SELECT DISTINCT pjuuid,1 as pwxk 
from zx_hb_paiwu_valid
where now() > yxqq  and  date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year) < yxqz

)
t2 
on t1.pjuuid = t2.pjuuid
) ,
-- 


-- count_hpsp 环评审批
count_hpsp as (
select t1.pjuuid,t2.count_hpsp 
from main t1  left join 

(SELECT distinct pjuuid, 1 as count_hpsp 
FROM zx_hb_xmhz_valid
where spsj between  date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)  and  str_to_date(now(),'%%Y-%%m-%%d')
) t2 on t1. pjuuid =t2.pjuuid 
) ,

-- 

ldbzcf_12m_cnt as 
(
select t1.pjuuid,t2.ldbzcf_12m_cnt
from main t1 left join 
(
SELECT pjuuid,1 as ldbzcf_12m_cnt  FROM
(
SELECT pjuuid,
			case when larq is null then sdcfwsrq else larq end as cfrq
from zx_rs_chufa_valid
) t
where  cfrq between  date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)   and str_to_date(now(),'%%Y-%%m-%%d')
) t2 
on t1.pjuuid = t2.pjuuid 

),
-- 


-- 
-- bldk_12m_cnt 企业逾期信息
-- 


bldk_12m_cnt as 
(
select 
t1.pjuuid ,t2.bldk_12m_cnt
from main t1 left join 
(
SELECT distinct pjuuid
			,1 as bldk_12m_cnt
from zx_rmyh_bldk_valid
where  dqrq between  date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)   and str_to_date(now(),'%%Y-%%m-%%d')
) t2 on  t1.pjuuid = t2.pjuuid 
),

-- 用水欠费信息 ysqfxx_12m_cnt
ysqfxx_12m_cnt as 
(
select t1.pjuuid , t2.ysqfxx_12m_cnt
from 
main t1 left join 
(
select 
pjuuid ,count(je) as  ysqfxx_12m_cnt
from 
(
select   
       pjuuid,
			 concat(substr(replace(tjny,'-',''),1,4),'-',substr(replace(tjny,'-',''),5,2),'-01') as dt ,
			 je	 
FROM legal_entity_water_arrearage_valid 
)	 t 
where str_to_date(dt,'%%Y-%%m-%%d')  between  date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)  and str_to_date(now(),'%%Y-%%m-%%d')  and je != 0 and je is  not null 
group by pjuuid 
) t2  on t1.pjuuid = t2.pjuuid 
),

-- 企业严重失信行为
qyyzsxxw_cnt_12m as 
(
SELECT  t1.pjuuid ,t2.qyyzsxxw_cnt_12m
from 
main t1 left join 
(
SELECT DISTINCT pjuuid
,1 as qyyzsxxw_cnt_12m
from legal_entity_break_faith_valid
where break_faith_level = '严重失信行为'
and STR_TO_DATE(break_faith_date,'%%Y-%%m-%%d')  between  date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)  and str_to_date(now(),'%%Y-%%m-%%d') 

) t2 on t1.pjuuid = t2.pjuuid

) ,

-- 司法协助 sfxz_cnt_12m
sfxz_cnt_12m as 
(
select  t1.pjuuid , t2.sfxz_cnt_12m
from 
main t1 
left join 
(
select * ,1 as sfxz_cnt_12m
from (
SELECT 
    pjuuid, 
    executedate AS dt, 
    exerulnum
FROM 
    cs_pub_justiceinfo_valid
WHERE 
    executeitem = 4
union all 
SELECT 
    pjuuid, 
    frofrom AS dt, 
    exerulnum
FROM 
    cs_pub_justiceinfo_valid
WHERE 
    executeitem = 1
) t
where str_to_date(dt,'%%Y-%%m-%%d') between  date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)  and str_to_date(now(),'%%Y-%%m-%%d') 

) t2 
on 
	t1.pjuuid = t2.pjuuid 
) ,

-- 
frbg_12m_cnt as 
(
SELECT
t1.pjuuid,t2.frbg_12m_cnt
FROM main t1 
left join 
(
SELECT pjuuid ,count(altdate) as frbg_12m_cnt
FROM
(
SELECT DISTINCT pjuuid, altitem, altdate
from cs_mid_altitem_valid
where altitem = 3 and str_to_date(altdate,'%%Y-%%m-%%d')  between  date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)  and str_to_date(now(),'%%Y-%%m-%%d') 

) t
GROUP BY pjuuid ) t2
on 
	t1.pjuuid = t2.pjuuid
	),

-- 股权转让、股权变更  gqzrgqbg_12m_cnt
gqzrgqbg_12m_cnt as 
(
SELECT t1.pjuuid ,t2.gqzrgqbg_12m_cnt
from 
main t1 
left join 
(
select pjuuid,1 as gqzrgqbg_12m_cnt
FROM cs_mid_altitem_valid
where altitem = '51'
and STR_TO_DATE(altdate,'%%Y-%%m-%%d') between  date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)  and str_to_date(now(),'%%Y-%%m-%%d') 
) t2 
on 
	t1.pjuuid = t2.pjuuid
	),
-- 
-- yclx_12m_max 工商信息异常
 yclx_12m_max as (
select t1.pjuuid ,t2.yclx_12m_max
from 
main t1 
left join 
(
SELECT DISTINCT pjuuid
                        ,case when specausecn like '%%年度报告%%' and specausecn not like '%%弄虚作假%%' then 1
                              when specausecn like '%%第十条%%' then 2
                              when specausecn like '%%联系%%' then 3
                              when specausecn like '%%弄虚作假%%' then 4
                              else 5 end as yclx_12m_max
                  from cs_pub_opadetail_valid
	where 
		str_to_date(substr(abntime,1,10),'%%Y-%%m-%%d')   between  date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)  and str_to_date(now(),'%%Y-%%m-%%d') 
		) t2 on t1.pjuuid = t2.pjuuid 
		),

gscf_new_cnt_12m as 
(
SELECT 
	 t1.pjuuid ,t2.gscf_new_cnt_12m
FROM
main t1 
left join 
(
SELECT DISTINCT pjuuid,1 as  gscf_new_cnt_12m from zx_ga_cfxx_valid
where 
	larq  between  date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)  and str_to_date(now(),'%%Y-%%m-%%d') 
) t2  on t1.pjuuid = t2.pjuuid 
),	
	-- 其他部门处罚 otherpun_cnt_12m
otherpun_cnt_12m as 
(
SELECT 
	 t1.pjuuid ,t2.otherpun_cnt_12m
FROM
main t1 
left join 
(
SELECT DISTINCT pjuuid, 1 as otherpun_cnt_12m
                  from cs_pub_otherpunish_valid

where 
	pendecissdate  between  date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)  and str_to_date(now(),'%%Y-%%m-%%d') 
) t2  on t1.pjuuid = t2.pjuuid 
),
-- qylach_12m_cnt 企业立案信息
qylach_12m_cnt as (
SELECT 
	 t1.pjuuid ,t2.qylach_12m_cnt
FROM
main t1 
left join 
(
SELECT DISTINCT pjuuid
										, 1 as qylach_12m_cnt
                  from zx_fy_qylaxx_valid
                  where ssdw in ('被告','被上诉人','被执行人','被申请执行人','被告人')
and
	larq  between  date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)  and str_to_date(now(),'%%Y-%%m-%%d') 
) t2  on t1.pjuuid = t2.pjuuid 
	),
	
count_hbcf as 
(
SELECT 
t1.pjuuid ,t2.count_hbcf
FROM 
main t1 
left join 
(
SELECT DISTINCT pjuuid,1 as count_hbcf
from zx_hb_cfxx_valid
where larq between  date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)  and str_to_date(now(),'%%Y-%%m-%%d') 
) t2 on t1.pjuuid = t2.pjuuid 
),

sxss as 
( 
select  t1.pjuuid ,t2.sxss
from 
main t1
left join 
(
SELECT pjuuid, 
case when sxss like '%%逃匿%%' then 4
when sxss like '%%转移%%' then 3
when sxss like '%%拒不%%' then 2
else 1 end as sxss
from zx_rs_qxqy_valid
where ccrq between date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)  and str_to_date(now(),'%%Y-%%m-%%d') 
) t2 on t1.pjuuid = t2.pjuuid 
),

gacf_12m_cnt as 
(
SELECT t1.pjuuid,t2.gacf_12m_cnt
FROM
main t1
left join 
(
SELECT DISTINCT pjuuid, 1 as gacf_12m_cnt from zx_ga_cfxx_valid
where larq between date_sub(str_to_date(now(),'%%Y-%%m-%%d'),interval 1 year)  and str_to_date(now(),'%%Y-%%m-%%d') 
) t2 on t1.pjuuid =t2.pjuuid
)


select 
			t.*,
			t0.pripid,
			t1.qycxrd_cnt_12m,
			t2.clns,
			t3.zybgcs_12m_cnt,
			t4.sum_yjse,
			t4.ttm_nsmonths,
			t5.pwxk,
			t6.count_hpsp,
			t7.count_hbcf,
			t8.ldbzcf_12m_cnt,
			t9.bldk_12m_cnt,
			t10.sxss,
			t11.ysqfxx_12m_cnt,
			t12.qyyzsxxw_cnt_12m,
			t13.sfxz_cnt_12m,
			t14.frbg_12m_cnt,
			t15.gqzrgqbg_12m_cnt,
			t16.yclx_12m_max,
			t17.gacf_12m_cnt,
			t18.gscf_new_cnt_12m,
			t19.otherpun_cnt_12m,
			t20.qylach_12m_cnt



from 
	target t
left join 
	main t0
on 
	t.pjuuid = t0.pjuuid
left join 
	qycxrd_cnt_12m t1 
on 
	t0.pjuuid = t1.pjuuid
left join 
	clns t2
on  
	t1.pjuuid = t2.pjuuid 
left join
	zybgcs_12m_cnt t3 
on 
	t1.pjuuid = t3.pjuuid 
left join 
	sum_yjse t4 
on 
	t1.pjuuid = t4.pjuuid 
left join 
	pwxk  t5 
on  
	t1.pjuuid = t5.pjuuid 
left join 
	count_hpsp t6 
on
	t1.pjuuid  = t6.pjuuid 
left join 
	count_hbcf t7
on 
	t1.pjuuid = t7.pjuuid
	
left join 
	ldbzcf_12m_cnt t8
on 
	t1.pjuuid = t8.pjuuid 
left join
	bldk_12m_cnt t9 
on 
	t1.pjuuid = t9.pjuuid 
	
left join 
  sxss t10 
on 
	t1.pjuuid = t10.pjuuid
	
	
left join 
	ysqfxx_12m_cnt t11
on 
	t1.pjuuid = t11.pjuuid 

left join 
	qyyzsxxw_cnt_12m t12
on
	t1.pjuuid = t12.pjuuid
	
left join 
	sfxz_cnt_12m t13
on 
	t1.pjuuid = t13.pjuuid
	
left join 
	frbg_12m_cnt t14

on 
	t1.pjuuid = t14.pjuuid

left join 
	gqzrgqbg_12m_cnt t15
on 
	t1.pjuuid = t15.pjuuid 

left join 
	yclx_12m_max t16 
on 
	t1.pjuuid = t16.pjuuid 

left join 
	gacf_12m_cnt t17
on 
	t1.pjuuid = t17.pjuuid
	
left join 
	gscf_new_cnt_12m t18
on 
	t1.pjuuid = t18.pjuuid
	
left join 
	otherpun_cnt_12m t19
on 
	t1.pjuuid = t19.pjuuid
	
left join 
	qylach_12m_cnt t20
on 
	t1.pjuuid = t20.pjuuid

                  '''
        dfo = pd.read_sql_query(sql_dfo,self.engine)
        return dfo

    def ToSql(self,df,table_name):
        print('')
        print('开始写入数据库')
        host, user, passwd, port, schema_database  = self.read_sql_param()
        rows = df.to_sql(table_name, con=self.engine, schema=schema_database, if_exists='append',
                         index=False)
        print('已经向{}中写入{}行'.format(table_name,rows))
    #
    def DelSql(self,batch_id):
        print('')
        print('开始删除数据')

        #
        # cursor = self.cursor
        # del_esg_rating_result = '''
        # delete from esg_rating_result
        # where batch_id = '{}';
        # '''.format(batch_id)
        # del_esg_rule_record = '''
        #         delete from esg_rule_record
        #         where batch_id = '{}';
        #         '''.format(batch_id)
        #
        # del_esg_index_result_score = '''
        #         delete from esg_index_result_score
        #         where batch_id = '{}';
        #         '''.format(batch_id)
        # del_esg_score_ratio = '''
        #                 delete from esg_score_ratio
        #                 where batch_id = '{}';
        #                 '''.format(batch_id)
        # print(del_esg_score_ratio)
        # cursor.execute(del_esg_rating_result)
        # cursor.execute('commit')
        # cursor.execute(del_esg_rule_record)
        # cursor.execute('commit')
        # cursor.execute(del_esg_index_result_score)
        # cursor.execute('commit')
        # cursor.execute(del_esg_score_ratio)
        # cursor.execute('commit')
        # cursor.close()
        del_sqls = [
            "delete from esg_rating_result where batch_id = '{batch_id}'",
            "delete from esg_rule_record where batch_id = '{batch_id}'",
            "delete from esg_index_result_score where batch_id = '{batch_id}'",
            "delete from esg_score_ratio where batch_id = '{batch_id}'"
        ]
        with self.engine.connect() as connection:
            for del_sql in del_sqls:
                sql = del_sql.format(batch_id=batch_id)
                result = connection.execute(sql)
                print(f'已删除 {result.rowcount} 行数据')
        print('删除成功')

    def get_sql_lst(self,id_lst):
        main = f'''
        CREATE TEMPORARY table main as (
                select distinct pjuuid,estdate,industryphy,industryco,reccaprmb,regcap,pripid
                from cs_mid_baseinfo_valid
                where estdate < date_sub(curdate(),interval 1 year)  and pjuuid in ({id_lst})

        ) ;'''

        pjjd_list2 = f'''
        CREATE TEMPORARY table pjjd_list2  as 
        (
        select 	
        distinct 
            pjuuid	,
            AZ1	,
            AZ2	,
            AZ3	,
            AZ4	,
            AZ5	,
            AZ6	,
            AZ7	,
            AZ8	,
            AZ9	,
            AZ10	,
            AZ11	,
            AZ12	,
            AZ13	,
            A0	,
            A1	,
            A2	,
            A3	,
            A4	,
            A5	,
            A6	,
            A7	,
            A8	,
            A9	,
            A10	,
            A11	,
            A12	,
            A13	,
            A14	,
            A16	,
            A18	,
            A20	,
            A21	,
            A22	,
            A23	,
            A24	,
            A25	,
            A26	,
            A27	,
            A28	,
            A29	,
            A30	,
            A31	,
            A32	,
            A33	,
            AZ14	
        from 	pjjd_list	
        where pjuuid is not null and pjuuid <> ''
        and pjuuid in ({id_lst})
        ); '''

        target = '''
        CREATE TEMPORARY table target as (


        SELECT
          t1.pjuuid,
            AZ2 as credit_id,
        case 			
        when cast(substr(AZ14,1,2) as signed)  in 	(1, 2, 3, 4, 5)	then 	"A"
        when cast(substr(AZ14,1,2) as signed)  in 	(6, 9, 12, 25, 30, 11)	then 	"B06091230C25"
        when cast(substr(AZ14,1,2) as signed)  in 	(13, 14, 15, 27, 41, 42, 16)	then 	"C13_15274142"
        when cast(substr(AZ14,1,2) as signed)  in 	(17, 18)	then 	"C1718"
        when cast(substr(AZ14,1,2) as signed)  in 	(19)	then 	"C19"
        when cast(substr(AZ14,1,2) as signed)  in 	(20, 21, 22, 23, 24)	then 	"C20_24"
        when cast(substr(AZ14,1,2) as signed)  in 	(26, 28)	then 	"C2628"
        when cast(substr(AZ14,1,2) as signed)  in 	(29)	then 	"C29"
        when cast(substr(AZ14,1,2) as signed)  in 	(31, 32, 33, 38, 39, 40, 37)	then 	"C31_3338_40"
        when cast(substr(AZ14,1,2) as signed)  in 	(34, 35)	then 	"C3435"
        when cast(substr(AZ14,1,2) as signed)  in 	(36)	then 	"C36"
        when cast(substr(AZ14,1,2) as signed)  in 	(43, 61, 62, 64, 65, 69, 70, 71, 72, 74, 75, 81, 82, 90)	then 	"C43_R90"
        when cast(substr(AZ14,1,2) as signed)  in 	(44, 45, 46)	then 	"D44_46"
        when cast(substr(AZ14,1,2) as signed)  in 	(49, 50, 47, 48)	then 	"E4950"
        when cast(substr(AZ14,1,2) as signed)  in 	(51, 52)	then 	"F"
        when cast(substr(AZ14,1,2) as signed)  in 	(53, 54, 55, 59, 60)	then 	"G53_5559_60"
        when cast(substr(AZ14,1,2) as signed)  in 	(58, 63, 73, 76, 77, 78, 79, 80, 83, 84, 85, 86, 87, 88, 89, 92)	then 	"G58_S92"
        else 'bup' end as model_type ,
            CASE
            WHEN A25 LIKE '%%是%%' THEN
            1 ELSE 0 
            END AS A25,-- 环境管理制度 A25
        CASE

            WHEN A6 LIKE '%%是%%' THEN
            1 ELSE 0 
            END AS A6, -- 是否生产绿色产品 A6
        case when  A7  like '是' or A8 like '是' then 1 else 0 end as cust , -- 下游应用方向 cust 
        case when A9 like '是' or A10  like '是' or A11 like '是' then 1 else 0 end  as supp ,  -- 节能环保原材料使用
        case when  A12 like '是' or A13  like '是' or A14 like '是' then 1 else 0 end  as equip, -- 低能耗生产设备使用
        case when  A16 != 'x3' then 1 else 0 end as feishui , -- 废水排放管理
        case when  A18 != 'x3' then 1 else 0 end as feiqi ,-- 废气排放管理
        case when  A20 != 'x3' then 1 else 0 end as feizha , -- 废渣排放管理
        CASE
            WHEN A21 = '否' THEN 1
            WHEN A21 != '否' AND A24 = '是' THEN 0.5
            WHEN A21 != '否' AND (A22 = '是' OR A23 = '是') THEN 1
            ELSE 0  end as weifei ,-- 危险废弃物排放管理 
        case when A26 = '是' then 1 else 0 end  as A26, -- A26是否采用电子化运营管理
        case when  A27 = '是' or  A28 = '是' then 1 else 0 end  as jianzhu, -- jianzhu 绿色建筑及建筑节能
        case when A29 = '是' or A30 = '是' then 1 else 0 end as shebei, -- shebei设备节能
        (
                CASE
                    WHEN A0 IS NOT NULL AND A0 <> '' AND A0 <> '非绿' THEN A2
                    ELSE 0
                END +
                CASE
                    WHEN A3 IS NOT NULL AND A3 <> '' AND A3 <> '非绿' THEN A5
                    ELSE 0
                END
            ) / 100.0 AS lyzb,  -- 绿营占比
          A31 / (GREATEST(
                COALESCE(t1.A33, 0),
                COALESCE(t2.xssr, 0),
                COALESCE(t3.vendinc, 0)
            ) * (100 - coalesce(AZ11,0)) / 100 ) AS ynqd, -- 用能强度
                A32 / (GREATEST(
                COALESCE(t1.A33, 0),
                COALESCE(t2.xssr, 0),
                COALESCE(t3.vendinc, 0)
            ) * (100 - coalesce(AZ11,0)) / 100 ) as ysqd   -- 用水强度

        from pjjd_list2 t1 

        left join

        (select pjuuid, substr(sbny,1,4) as sbny ,sum(xssr) as xssr  
        from 
        zx_swg_nsxx_xssr_valid
        where substr(sbny,1,4) >  year(date_sub(curdate(),interval 10 year))-- year(date_sub(now(),interval 10 year))
        group by pjuuid,substr(sbny,1,4) 
        )

        t2

        on  t1.AZ13 = t2.sbny and t1.pjuuid = t2.pjuuid

        left join 

        (select pjuuid,substr(year,1,4) as year ,sum(VENDINC) as VENDINC
        from cs_yr_asset_valid 
        where substr(year,1,4) >  year(date_sub(curdate(),interval 10 year)) -- year(DATE_SUB(now(),interval 10 year))
        group by pjuuid,substr(year,1,4) 
        ) t3 
        on t1.AZ13 = t3.year and t1.pjuuid = t3.pjuuid
        where t1.pjuuid is not null and t1.pjuuid != ''


        );'''

        qycxrd_cnt_12m = '''
        CREATE TEMPORARY table qycxrd_cnt_12m AS (
        -- 企业诚信认定
        select t1.pjuuid,t2.qycxrd_cnt_12m  
        from 
        main t1  left join 
        (
        SELECT DISTINCT
            pjuuid,
            rdsj as dt ,
            1 as qycxrd_cnt_12m
        FROM
            zx_gsj_xyglsfqy_valid 
        WHERE rdsj = year(date_sub(curdate(),interval 1 year)) 

        UNION ALL

        SELECT DISTINCT
            pjuuid,
            nd dt ,
            1 as qycxrd_cnt_12m
        FROM
            zx_gsj_szmdxx_valid
        WHERE nd = year(date_sub(curdate(),interval 1 year)) 

        ) t2 on  t1.pjuuid = t2.pjuuid
        );'''

        clns = '''

        CREATE TEMPORARY table clns AS (
        select pjuuid ,
                        floor(DATEDIFF(curdate(),estdate)/ 365 ) AS clns
        from 
            main 
        );
        '''

        zybgcs_12m_cnt = '''
        -- 企业主营范围变更次数
        CREATE TEMPORARY table zybgcs_12m_cnt as (
        select t1.pjuuid ,t2.zybgcs_12m_cnt
        from 
        main t1 
        left join 
        (
        SELECT DISTINCT pjuuid,1 as zybgcs_12m_cnt 
        from cs_mid_altitem_valid
        where altitem = 12
        and  datediff(now(),altdate) < 365  and datediff(now(),altdate) > 0
        ) t2  

        on t1.pjuuid = t2.pjuuid 
        );'''

        sum_yjse = ''' 
        CREATE TEMPORARY table sum_yjse as 
        (
        SELECT
            t1.pjuuid,
            sum( t2.yjse ) AS sum_yjse ,
            count(t2.yjse) as ttm_nsmonths
        FROM
            main t1
            LEFT JOIN (
        SELECT
            pjuuid,
            yjse 
        FROM
            zx_swg_nsxx_sk_valid 
        WHERE
            datediff( now(), str_to_date( concat( rkny, '-01' ), '%%Y-%%m-%%d' ) ) < 365 AND datediff( now(), str_to_date( concat( rkny, '-01' ), '%%Y-%%m-%%d' ) ) > 0 
            ) t2 ON t1.pjuuid = t2.pjuuid 
        GROUP BY
            t1.pjuuid
        );
         '''

        pwxk = '''
        -- pwxk  排污许可
        CREATE TEMPORARY table pwxk as (
        select t1.pjuuid ,t2.pwxk
        from main t1 left join 
        (
        SELECT DISTINCT pjuuid,1 as pwxk 
        from zx_hb_paiwu_valid
        where now() > yxqq  and  date_sub(curdate(),interval 1 year) < yxqz

        )
        t2 
        on t1.pjuuid = t2.pjuuid
        ) ;'''

        count_hpsp = '''
        CREATE TEMPORARY table count_hpsp as (
        select t1.pjuuid,t2.count_hpsp 
        from main t1  left join 

        (SELECT distinct pjuuid, 1 as count_hpsp 
        FROM zx_hb_xmhz_valid
        where spsj between  date_sub(curdate(),interval 1 year)  and  curdate()
        ) t2 on t1. pjuuid =t2.pjuuid 
        ) ;
        '''

        ldbzcf_12m_cnt = '''
        CREATE TEMPORARY table ldbzcf_12m_cnt as 
        (
        select t1.pjuuid,t2.ldbzcf_12m_cnt
        from main t1 left join 
        (
        SELECT pjuuid,1 as ldbzcf_12m_cnt  FROM
        (
        SELECT pjuuid,
                    case when larq is null then sdcfwsrq else larq end as cfrq
        from zx_rs_chufa_valid
        ) t
        where  cfrq between  date_sub(curdate(),interval 1 year)   and curdate()
        ) t2 
        on t1.pjuuid = t2.pjuuid 

        );'''

        bldk_12m_cnt = '''
        CREATE TEMPORARY table bldk_12m_cnt as 
        (
        select 
        t1.pjuuid ,t2.bldk_12m_cnt
        from main t1 left join 
        (
        SELECT distinct pjuuid
                    ,1 as bldk_12m_cnt
        from zx_rmyh_bldk_valid
        where  dqrq between  date_sub(curdate(),interval 1 year)   and curdate()
        ) t2 on  t1.pjuuid = t2.pjuuid 
        );'''

        ysqfxx_12m_cnt = '''
        -- 用水欠费信息 ysqfxx_12m_cnt
        CREATE TEMPORARY table ysqfxx_12m_cnt as 
        (
        select t1.pjuuid , t2.ysqfxx_12m_cnt
        from 
        main t1 left join 
        (
        select 
        pjuuid ,count(je) as  ysqfxx_12m_cnt
        from 
        (
        select   
               pjuuid,
                                     CASE 
                            WHEN CAST(SUBSTR(REPLACE(tjny, '-', ''), 5, 2) AS UNSIGNED) BETWEEN 1 AND 12 THEN
                                DATE(CONCAT(SUBSTR(REPLACE(tjny, '-', ''), 1, 4), '-', SUBSTR(REPLACE(tjny, '-', ''), 5, 2), '-01'))
                            ELSE
                                NULL
                        END AS dt,
                     je	 
        FROM legal_entity_water_arrearage_valid 
        )	 t 
        where dt  between  date_sub(curdate(),interval 1 year)  and curdate()  and je != 0 and je is  not null 
        group by pjuuid 
        ) t2  on t1.pjuuid = t2.pjuuid 
        );'''

        qyyzsxxw_cnt_12m = '''
        -- 企业严重失信行为
        CREATE TEMPORARY table qyyzsxxw_cnt_12m as 
        (
        SELECT  t1.pjuuid ,t2.qyyzsxxw_cnt_12m
        from 
        main t1 left join 
        (
        SELECT DISTINCT pjuuid
        ,1 as qyyzsxxw_cnt_12m
        from legal_entity_break_faith_valid
        where break_faith_level = '严重失信行为'
        and STR_TO_DATE(break_faith_date,'%%Y-%%m-%%d')  between  date_sub(curdate(),interval 1 year)  and curdate() 

        ) t2 on t1.pjuuid = t2.pjuuid

        ) ;'''

        sfxz_cnt_12m = '''

        -- 司法协助 sfxz_cnt_12m
        CREATE TEMPORARY table sfxz_cnt_12m as 
        (
        select  t1.pjuuid , t2.sfxz_cnt_12m
        from 
        main t1 
        left join 
        (
        select * ,1 as sfxz_cnt_12m
        from (
        SELECT 
            pjuuid, 
            executedate AS dt, 
            exerulnum
        FROM 
            cs_pub_justiceinfo_valid
        WHERE 
            executeitem = 4
        union all 
        SELECT 
            pjuuid, 
            frofrom AS dt, 
            exerulnum
        FROM 
            cs_pub_justiceinfo_valid
        WHERE 
            executeitem = 1
        ) t
        where str_to_date(dt,'%%Y-%%m-%%d') between  date_sub(curdate(),interval 1 year)  and curdate() 

        ) t2 
        on 
            t1.pjuuid = t2.pjuuid 
        ) ;
        '''

        frbg_12m_cnt = '''
        CREATE TEMPORARY table frbg_12m_cnt as 
        (
        SELECT
        t1.pjuuid,t2.frbg_12m_cnt
        FROM main t1 
        left join 
        (
        SELECT pjuuid ,count(altdate) as frbg_12m_cnt
        FROM
        (
        SELECT DISTINCT pjuuid, altitem, altdate
        from cs_mid_altitem_valid
        where altitem = 3 and str_to_date(altdate,'%%Y-%%m-%%d')  between  date_sub(curdate(),interval 1 year)  and curdate() 

        ) t
        GROUP BY pjuuid ) t2
        on 
            t1.pjuuid = t2.pjuuid
            );'''

        gqzrgqbg_12m_cnt = '''
        -- 股权转让、股权变更  gqzrgqbg_12m_cnt
        CREATE TEMPORARY table gqzrgqbg_12m_cnt as 
        (
        SELECT t1.pjuuid ,t2.gqzrgqbg_12m_cnt
        from 
        main t1 
        left join 
        (
        select pjuuid,1 as gqzrgqbg_12m_cnt
        FROM cs_mid_altitem_valid
        where altitem = '51'
        and STR_TO_DATE(altdate,'%%Y-%%m-%%d') between  date_sub(curdate(),interval 1 year)  and curdate() 
        ) t2 
        on 
            t1.pjuuid = t2.pjuuid
            );'''

        yclx_12m_max = '''
        -- yclx_12m_max 工商信息异常
         CREATE TEMPORARY table yclx_12m_max as (
        select t1.pjuuid ,t2.yclx_12m_max
        from 
        main t1 
        left join 
        (
        SELECT DISTINCT pjuuid
                                ,case when specausecn like '%%年度报告%%' and specausecn not like '%%弄虚作假%%' then 1
                                      when specausecn like '%%第十条%%' then 2
                                      when specausecn like '%%联系%%' then 3
                                      when specausecn like '%%弄虚作假%%' then 4
                                      else 5 end as yclx_12m_max
                          from cs_pub_opadetail_valid
            where 
                str_to_date(substr(abntime,1,10),'%%Y-%%m-%%d')   between  date_sub(curdate(),interval 1 year)  and curdate() 
                ) t2 on t1.pjuuid = t2.pjuuid 
                );'''

        gscf_new_cnt_12m = '''
        CREATE TEMPORARY table gscf_new_cnt_12m as 
        (
        SELECT 
             t1.pjuuid ,t2.gscf_new_cnt_12m
        FROM
        main t1 
        left join 
        (
        SELECT DISTINCT pjuuid,1 as  gscf_new_cnt_12m from zx_ga_cfxx_valid
        where 
            larq  between  date_sub(curdate(),interval 1 year)  and curdate() 
        ) t2  on t1.pjuuid = t2.pjuuid 
        );'''

        otherpun_cnt_12m = '''CREATE TEMPORARY table otherpun_cnt_12m as 
        (
        SELECT 
             t1.pjuuid ,t2.otherpun_cnt_12m
        FROM
        main t1 
        left join 
        (
        SELECT DISTINCT pjuuid, 1 as otherpun_cnt_12m
                          from cs_pub_otherpunish_valid

        where 
            pendecissdate  between  date_sub(curdate(),interval 1 year)  and curdate() 
        ) t2  on t1.pjuuid = t2.pjuuid 
        );'''

        qylach_12m_cnt = '''
        CREATE TEMPORARY table qylach_12m_cnt as (
        SELECT 
             t1.pjuuid ,t2.qylach_12m_cnt
        FROM
        main t1 
        left join 
        (
        SELECT DISTINCT pjuuid
                                                , 1 as qylach_12m_cnt
                          from zx_fy_qylaxx_valid
                          where ssdw in ('被告','被上诉人','被执行人','被申请执行人','被告人')
        and
            larq  between  date_sub(curdate(),interval 1 year)  and curdate() 
        ) t2  on t1.pjuuid = t2.pjuuid 
            );'''

        count_hbcf = '''
        CREATE TEMPORARY table count_hbcf as 
        (
        SELECT 
        t1.pjuuid ,t2.count_hbcf
        FROM 
        main t1 
        left join 
        (
        SELECT DISTINCT pjuuid,1 as count_hbcf
        from zx_hb_cfxx_valid
        where larq between  date_sub(curdate(),interval 1 year)  and curdate() 
        ) t2 on t1.pjuuid = t2.pjuuid 
        );'''

        sxss = '''
        CREATE TEMPORARY table sxss as 
        ( 
        select  t1.pjuuid ,t2.sxss
        from 
        main t1
        left join 
        (
        SELECT pjuuid, 
        case when sxss like '%%逃匿%%' then 4
        when sxss like '%%转移%%' then 3
        when sxss like '%%拒不%%' then 2
        else 1 end as sxss
        from zx_rs_qxqy_valid
        where ccrq between date_sub(curdate(),interval 1 year)  and curdate() 
        ) t2 on t1.pjuuid = t2.pjuuid 
        );'''

        gacf_12m_cnt = '''
        CREATE TEMPORARY table gacf_12m_cnt as 
        (
        SELECT t1.pjuuid,t2.gacf_12m_cnt
        FROM
        main t1
        left join 
        (
        SELECT DISTINCT pjuuid, 1 as gacf_12m_cnt from zx_ga_cfxx_valid
        where larq between date_sub(curdate(),interval 1 year)  and curdate() 
        ) t2 on t1.pjuuid =t2.pjuuid
        );'''

        sql_concat = '''
        select 
                    t.*,
                    t0.pripid,
                    t1.qycxrd_cnt_12m,
                    t2.clns,
                    t3.zybgcs_12m_cnt,
                    t4.sum_yjse,
                    t4.ttm_nsmonths,
                    t5.pwxk,
                    t6.count_hpsp,
                    t7.count_hbcf,
                    t8.ldbzcf_12m_cnt,
                    t9.bldk_12m_cnt,
                    t10.sxss,
                    t11.ysqfxx_12m_cnt,
                    t12.qyyzsxxw_cnt_12m,
                    t13.sfxz_cnt_12m,
                    t14.frbg_12m_cnt,
                    t15.gqzrgqbg_12m_cnt,
                    t16.yclx_12m_max,
                    t17.gacf_12m_cnt,
                    t18.gscf_new_cnt_12m,
                    t19.otherpun_cnt_12m,
                    t20.qylach_12m_cnt



        from 
            target t
        left join 
            main t0
        on 
            t.pjuuid = t0.pjuuid
        left join 
            qycxrd_cnt_12m t1 
        on 
            t0.pjuuid = t1.pjuuid
        left join 
            clns t2
        on  
            t1.pjuuid = t2.pjuuid 
        left join
            zybgcs_12m_cnt t3 
        on 
            t1.pjuuid = t3.pjuuid 
        left join 
            sum_yjse t4 
        on 
            t1.pjuuid = t4.pjuuid 
        left join 
            pwxk  t5 
        on  
            t1.pjuuid = t5.pjuuid 
        left join 
            count_hpsp t6 
        on
            t1.pjuuid  = t6.pjuuid 
        left join 
            count_hbcf t7
        on 
            t1.pjuuid = t7.pjuuid

        left join 
            ldbzcf_12m_cnt t8
        on 
            t1.pjuuid = t8.pjuuid 
        left join
            bldk_12m_cnt t9 
        on 
            t1.pjuuid = t9.pjuuid 

        left join 
          sxss t10 
        on 
            t1.pjuuid = t10.pjuuid


        left join 
            ysqfxx_12m_cnt t11
        on 
            t1.pjuuid = t11.pjuuid 

        left join 
            qyyzsxxw_cnt_12m t12
        on
            t1.pjuuid = t12.pjuuid

        left join 
            sfxz_cnt_12m t13
        on 
            t1.pjuuid = t13.pjuuid

        left join 
            frbg_12m_cnt t14

        on 
            t1.pjuuid = t14.pjuuid

        left join 
            gqzrgqbg_12m_cnt t15
        on 
            t1.pjuuid = t15.pjuuid 

        left join 
            yclx_12m_max t16 
        on 
            t1.pjuuid = t16.pjuuid 

        left join 
            gacf_12m_cnt t17
        on 
            t1.pjuuid = t17.pjuuid

        left join 
            gscf_new_cnt_12m t18
        on 
            t1.pjuuid = t18.pjuuid

        left join 
            otherpun_cnt_12m t19
        on 
            t1.pjuuid = t19.pjuuid

        left join 
            qylach_12m_cnt t20
        on 
            t1.pjuuid = t20.pjuuid;'''

        sql_lst = [main, pjjd_list2, target, qycxrd_cnt_12m, clns, zybgcs_12m_cnt, sum_yjse, pwxk, count_hpsp,
                   ldbzcf_12m_cnt,
                   bldk_12m_cnt, ysqfxx_12m_cnt, qyyzsxxw_cnt_12m, sfxz_cnt_12m, frbg_12m_cnt, gqzrgqbg_12m_cnt,
                   yclx_12m_max,
                   gscf_new_cnt_12m, otherpun_cnt_12m, qylach_12m_cnt, count_hbcf, sxss, gacf_12m_cnt]
        return sql_lst, sql_concat


#%%

